package API

import (
	"Library_Management_System/model"
	"Library_Management_System/utils"
	sql2 "database/sql"
	"errors"
	"time"
)

// CheckUserLogin 根据用户名和密码进行登录
func CheckUserLogin(username, password, email string) (*model.User, error) {
	// sql语句
	sql := "select * from users where username =? and password = ? and email = ?"
	row := utils.Db.QueryRow(sql, username, password, email)
	user := &model.User{}
	if row != nil {
		err := row.Scan(&user.ID, &user.Username, &user.Password, &user.Email, &user.IsSuspended, &user.ViolationCount, &user.LastLoginTime, &user.IsAdmin)
		if err != nil {
			return nil, err
		}
		return user, nil
	}
	return nil, errors.New("登录失败！")
}

// Register 用户注册
func Register(username, password, email string, isAdmin bool) error {
	// sql语句
	sql := "insert into users(username, password, email, is_admin, last_login_time) values(?,?,?,?,?)"
	_, err := utils.Db.Exec(sql, username, password, email, isAdmin, time.Now().Format("2006-01-02 15:04:05"))
	if err != nil {
		return err
	}
	return nil
}

// CheckUsername 检查用户名是否存在
func CheckUsername(username string) bool {
	// sql语句
	sql := "select * from users where username =?"
	row := utils.Db.QueryRow(sql, username)
	// 查询row是否有结果
	var result interface{}
	err := row.Scan(&result)
	if err != nil {
		if err == sql2.ErrNoRows {
			return false
		}
	}
	return true
}

// GetUserByID 根据用户ID查询用户信息
func GetUserByID(userID string) (*model.User, error) {
	// sql语句
	sql := "select * from users where id =?"
	row := utils.Db.QueryRow(sql, userID)
	user := &model.User{}

	err := row.Scan(&user.ID, &user.Username, &user.Password, &user.Email, &user.IsSuspended, &user.ViolationCount, &user.LastLoginTime, &user.IsAdmin)
	if err != nil {
		return nil, err
	}
	return user, nil
}

// UpdateBorrowInfo 借阅图书
func UpdateBorrowInfo(userID, bookID, bookName, borrowDate, returnDate string) error {
	// sql语句 向图书借阅表插入相关信息
	sqlStr1 := "insert into borrow_records (user_id, book_id, book_name, borrow_date, return_date) values (?, ?, ?, ?, ?)"
	// sql语句 根据图书ID更新图书借出数量，使借出数量加一
	sqlStr2 := "update books set remaining_quantity = remaining_quantity + 1 where id =?"

	// 开启事务
	tx, err := utils.Db.Begin()
	if err != nil {
		return err
	}
	// 执行插入操作
	_, err = utils.Db.Exec(sqlStr1, userID, bookID, bookName, borrowDate, returnDate)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	// 执行更新操作
	_, err = utils.Db.Exec(sqlStr2, bookID)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	// 提交事务
	err = tx.Commit()
	if err != nil {
		return err
	}

	return nil
}

// DeleteBorrowInfo 归还图书
func DeleteBorrowInfo(userID, bookName string) error {
	// sql语句
	sqlStr1 := "delete from borrow_records where user_id = ? and book_name = ?"
	sqlStr2 := "update books set remaining_quantity = remaining_quantity - 1 where title =?"

	// 开启事务
	tx, err := utils.Db.Begin()
	if err != nil {
		return err
	}
	// 执行删除操作
	_, err = utils.Db.Exec(sqlStr1, userID, bookName)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	// 执行更新操作
	_, err = utils.Db.Exec(sqlStr2, bookName)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	// 提交事务
	err = tx.Commit()
	if err != nil {
		return err
	}

	return nil
}

// GetAllUsersInfo 获取所有用户信息
func GetAllUsersInfo() ([]*model.User, error) {
	// sql语句
	sql := "select id, username, password, email, violation_count, last_login_time from users where is_admin = 0"
	rows, err := utils.Db.Query(sql)
	if err != nil {
		return nil, err
	}

	var users []*model.User
	for rows.Next() {
		user := &model.User{}
		err := rows.Scan(&user.ID, &user.Username, &user.Password, &user.Email, &user.ViolationCount, &user.LastLoginTime)
		if err != nil {
			return nil, err
		}
		users = append(users, user)
	}
	return users, nil
}

// DisableUser 禁用用户
func DisableUser(userID string) error {
	// sql语句
	sql := "update users set is_suspended = 1, violation_count = violation_count + 1 where id = ?"
	_, err := utils.Db.Exec(sql, userID)
	if err != nil {
		return err
	}
	return nil
}

// GetAllBorrowInfo 获取用户借阅信息
func GetAllBorrowInfo() ([]*model.BorrowInfo, error) {
	// sql语句
	sql := "select b.id, b.user_id, u.username, b.book_id, b.book_name, b.borrow_date, b.return_date, b.is_overdue from borrow_records as b join users as u on b.user_id = u.id"
	rows, err := utils.Db.Query(sql)
	if err != nil {
		return nil, err
	}
	var borrowInfos []*model.BorrowInfo
	for rows.Next() {
		borrowInfo := &model.BorrowInfo{}
		err := rows.Scan(&borrowInfo.ID, &borrowInfo.UserID, &borrowInfo.UserName, &borrowInfo.BookID, &borrowInfo.BookName, &borrowInfo.BorrowDate, &borrowInfo.ReturnDate, &borrowInfo.IsOverdue)
		if err != nil {
			return nil, err
		}
		borrowInfos = append(borrowInfos, borrowInfo)
	}
	return borrowInfos, nil
}
